﻿# -*- coding: utf-8 -*-
"""
    Main model container. Describes majority of classes.
"""

import hashlib
import re
from decimal import Decimal
from datetime import datetime, date, timedelta

from sqlobject import SingleJoin, DatabaseIndex, MultipleJoin, RelatedJoin, SQLObjectNotFound, dberrors
from sqlobject.col import UnicodeCol, BoolCol, ForeignKey, DateTimeCol, DateCol, TimeCol, IntCol, BLOBCol, DecimalCol
from sqlobject.sqlbuilder import AND, SQLConstant

from mnemoply.utils import MonthYear, Wrapper, shift_date, getOne, format_currency, format_date, applyDiscont, getTime, isIntersect
from mnemoply.model.model_object import ModelObject, ModelObjectWithOwner
from mnemoply.model.columns import GenderCol, DiscontCol, CurrencyCol
from mnemoply.model import constants
from mnemoply.model import columns
from mnemoply.model import project_defence
from mnemoply.model import discont
from mnemoply.model import agreement
from mnemoply.common_knowledge import day_captions
from mnemoply.model.hub import __connection__

class BLOB(ModelObject):
    """
        Binary large object representation. Also see http://en.wikipedia.org/wiki/Binary_large_object
    """

    model_title = _("BLOB")
    class sqlmeta:
        table = "p_blob"

    hash = UnicodeCol(length=100, notNull=True, title=_("Hash"), alternateID=True)
    data = BLOBCol(varchar=False, length=2 ** 24, default=None, title=_("Data"))

    title = property(lambda self: self.hash)

    @classmethod
    def getHash(cls, data):
        h = hashlib.sha224()
        h.update(data)
        return h.hexdigest()

    @classmethod
    def getBLOB(cls, data):
        hash = cls.getHash(data)

        try:
            return cls.byHash(hash)
        except SQLObjectNotFound:
            return cls(hash=hash, data=data)

    @classmethod
    def substituteBLOBs(cls, text):
        return re.sub("{{BLOB:(\w+)}}", lambda match: cls.byHash(match.group(1)).data, text)

# Dictionaries
class Faculty(ModelObject):
    """
        Education department representation.
    """
    model_title = _("Faculty")
    name = UnicodeCol(length=50, notNull=True, alternateID=True, title=_("Name"))
    abbr = UnicodeCol(length=3, notNull=True, unique=True, title=_("Letter"))
    title = property(lambda self: self.name)

class PaymentType(ModelObject):
    """
        this is one of the characteristics of trend 
    """
    model_title = _("Payment type")
    name = UnicodeCol(length=50, notNull=True, alternateID=True, title=_("Name"))
    abbr = UnicodeCol(length=3, notNull=True, unique=True, title=_("Letter"))
    title = property(lambda self: self.name)
   
class Trend(ModelObject):
    """
        Education trend representation.
    """

    model_title = _("Trend")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    oneChar = UnicodeCol(length=10, notNull=True, unique=True, title=_("Letter"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    paymentType = ForeignKey('PaymentType', notNull=True, title=_("Payment type"))
    faculty = ForeignKey('Faculty', notNull=True, title=_("Faculty"))
    title = property(lambda self: self.name)

class TestType(ModelObject):
    """
        Type of scholarship tests.
    """

    model_title = _("Test type")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

class DischargeReason(ModelObject):
    """
        Describe why we discharge student from group.
    """

    model_title = _("Discharge reason")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

class MarkType(ModelObject):
    """
        Student mark type.
    """

    model_title = _("Mark type")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    name_ms = UnicodeCol(length=80, notNull=True, default='', title=_("MS IT name"))
    name_ms_rus = UnicodeCol(length=80, notNull=True, default='', title=_("MS IT name (russian)"))
    name_rus = UnicodeCol(length=80, notNull=True, default='', title=_("Name (russian)"))
    bad_mark = BoolCol(notNull=True, default=False, title=_("Bad mark?"))
    hide_mark = BoolCol(notNull=True, default=False, title=_("Hide mark?"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

class BusynessType(ModelObject):
    """
        Full employment or underemployment. Really need for persons.
    """

    model_title = _("Busyness type")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

class ClassroomType(ModelObject):
    """
        Maybe standard or multimedia.
    """

    model_title = _("Classroom type")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

class Organization(ModelObject):
    """
        Simple organization representation.
        Contains information for reports, such as address or some codes.
    """

    model_title = _("Organization")

    name = UnicodeCol(length=100, notNull=True, alternateID=True, title=_("Name"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

    fullName = UnicodeCol(default='', notNull=True, title=_("Full name"))
    address = UnicodeCol(default='', notNull=True, title=_("Address"))

    codeOcato = UnicodeCol(length=100, notNull=True, title=_("OCATO"))
    codeOcpo = UnicodeCol(length=100, notNull=True, title=_("OCPO"))
    codeInn = UnicodeCol(length=100, notNull=True, title=_("INN"))
    codeKpp = UnicodeCol(length=100, notNull=True, title=_("KPP"))

    directorName = UnicodeCol(length=100, notNull=True, title=_("Director name"))
    regalia = UnicodeCol(length=100, notNull=True, title=_("Regalia"))

class AgreementType(ModelObject):
    """
        Describes short-time or standard-long courses.
    """

    model_title = _("Agreement type")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    title = property(lambda self: self.name)

class CertificateType(ModelObject):
    """
        Certificate type. Need for English groups.
    """

    model_title = _("Certificate type")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

class Certificate(ModelObject):
    """
        Certificate. Need for English groups.
    """

    model_title = _("Certificate")

    name = UnicodeCol(length=80, notNull=True, title=_("Name"))
    certificateType = ForeignKey('CertificateType', notNull=True, title=_("Certificate type"))
    ordinal = IntCol(notNull=True, title=_("Certificate ordinal"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: "%s (%s)" % (self.name, self.certificateType.title))

class TestName(ModelObject):
    """
        Test. Test work by some plan.
    """

    model_title = _("Test name")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    plan = ForeignKey('Plan', notNull=True, title=_("Plan"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    title = property(lambda self: self.name)

class Preorder(ModelObject):
    """
        We create it before order.
        After fixation all students from this preorder moved to new order.
    """

    model_title = _("Preorder")

    creationDate = DateTimeCol(notNull=True, default=datetime.now, title=_("Creation date"))
    lastUpdate = DateTimeCol(notNull=True, default=datetime.now, title=_("Last update"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    pTypeTag = UnicodeCol(length=30, default='', notNull=True, title=_("Preorder type"))
    owner = ForeignKey('AuthUser', notNull=True, title=_("Owner"))

    @classmethod
    def selectByPTypeTag(cls, tag):
        return cls.selectBy(pTypeTag=tag)

    def getTitle(self):
        from mnemoply import preorders
        x = "%s %s: %s" % (self.owner.title, self.creationDate, self.pTypeTag)
        if len(self.comment) > 0:
            x += " (%s)" % self.comment
        return x
    title = property(getTitle)

class Order(ModelObject):
    """
        Contains information about students transfers, discharges and group inclusions.
    """

    model_title = _("Order")
    class sqlmeta:
        table = "p_order"

    # data for report
    rawData = BLOBCol(varchar=True, length=2 ** 24, default=None, title=_("Data"))

    creationDate = DateCol(notNull=True, default=lambda: date.today(), title=_("Creation date"))
    title = UnicodeCol(length=200, notNull=True, title=_("Title"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    createdBy = ForeignKey('AuthUser', notNull=True, title=_("Created by"))
    fixedBy = ForeignKey('AuthUser', notNull=True, title=_("Fixed by"))

    def setData(self, data):
        self.rawData = re.sub(r"<data>(.*)</data>",
            lambda match: "<data>{{BLOB:%s}}</data>" % BLOB.getBLOB(match.group(1)).hash,
            data)

    def getData(self):
        return BLOB.substituteBLOBs(self.rawData)

    data = property(getData, setData)

class Student(ModelObject):
    """
        Student representation.
    """

    model_title = _("Student")

    surname = UnicodeCol(length=80, notNull=True, title=_("Surname"))
    forename = UnicodeCol(length=80, notNull=True, title=_("Forename"))
    patronymic = UnicodeCol(length=80, default='', notNull=True, title=_("Patronymic"))
    birthday = DateCol(notNull=True, title=_("Birthday"))
    gender = GenderCol(notNull=True, title=_("Gender"))
    enteringDate = DateCol(notNull=True, title=_("Entering date"), default=lambda: date.today())
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    email = UnicodeCol(length=140, default='', notNull=True, title=_("EMail"))

    # Transliterate student name
    surname_translit = UnicodeCol(length=80, default='', notNull=True, title=_("Surname translit"))
    forename_translit = UnicodeCol(length=80, default='', notNull=True, title=_("Forename translit"))
    patronymic_translit = UnicodeCol(length=80, default='', notNull=True, title=_("Patronymic translit"))

    # Applicant's data
    schoolName = UnicodeCol(length=80, default='', notNull=True, title=_("School name"))
    schoolForm = IntCol(default=None, title=_("School form"))

    uniqueStudent = DatabaseIndex('surname', 'forename', 'patronymic', 'birthday', unique=True)

    # Cached fields
    cacheInGroups = IntCol(notNull=True, default=0)
    cacheGroups = UnicodeCol(notNull=True, default='', length=200)
    cacheBalance = CurrencyCol(notNull=True, default=0)

    # State flags
    isEducationFinished = BoolCol(notNull=True, default=False)

    title = property(lambda self: "%s %s %s" % (self.surname, self.forename, format_date(self.birthday)))
    name = title
    fullName = property(lambda self: "%s %s %s" % (self.surname, self.forename, self.patronymic))

    def getMarks(self, only_bad_marks=False):
        marks = Mark.selectBy(student=self)

        if only_bad_marks:
            marks = [m for m in marks if m.markType.bad_mark]

        return marks

    marks = property(getMarks)

class CantGuessOfficeException(Exception):
    def __init__(self, id):
        self.student_id = id
        self.value = "Can't guess student's office"

    def __str__(self):
        return repr(self.value)

class CantGuessTrendException(Exception):
    def __init__(self, id):
        self.student_id = id
        self.value = "Can't guess student's trend"

    def __str__(self):
        return repr(self.value)

class Office(ModelObject):
    """
        UCVT's office.
        Abbreviation need for partner's password generation.
    """

    model_title = _("Office")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    ordinal = IntCol(notNull=True, title=_("Ordinal"))
    address = UnicodeCol(length=120, notNull=True, title=_("Address"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    abbreviation = UnicodeCol(length=80, default='', title=_("Abbreviation"))

    ordinalUnique = DatabaseIndex('ordinal', unique=True)

    title = property(lambda self: self.name)

class Classroom(ModelObject):
    """
        Classroom in office.
    """

    model_title = _("Classroom")

    office = ForeignKey('Office', notNull=True, title=_("Office"))
    classroomType = ForeignKey('ClassroomType', notNull=True, title=_("Classroom type"))
    roomNumber = IntCol(notNull=True, title=_("Room #"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    numberInOfficeUnique = DatabaseIndex('office', 'roomNumber', unique=True)

    title = property(lambda self: "%s %s, %s" % (_("Room #"), self.roomNumber, self.office.name))

    def getLessonsByDayOfWeek(self, dayOfWeek):
        """
            Returns open groups lessons.
        """

        lessons = [l for l in LessonInSchedule.selectBy(classroom=self, dayOfWeek=dayOfWeek) if l.group.isOpen]
        lessons.sort(key=lambda x: x.getStartTimeStr())
        return lessons

    def getLessonsByDate(self, d):
        """
            Returns open groups lessons by date from worked groups.
        """
        return [l for l in self.getLessonsByDayOfWeek(d.weekday() + 1) if l.group.isOpen and l.group.startDate <= d <= l.group.endDate]

    def getLessonsByDateTime(self, d, start_time, end_time):
        """
            Returns open groups lessons by date from worked groups
            and lessons which entirely matches to specified time interval
            Parameters:
            d - date (datetime.date not datetime.datetime.date)
            start_time - int (hour)
            end_time - int (hour)
        """
        return [l for l in self.getLessonsByDayOfWeek(d.weekday() + 1)
                if l.group.isOpen and
                   l.group.startDate <= d <= l.group.endDate and
                   start_time <= l.startTimeHour <= end_time and
                   start_time <= l.getEndLessonHour() <= end_time
        ]

class Topic(ModelObject):
    """
        Education topic.
    """

    model_title = _("Topic")

    name = UnicodeCol(length=180, notNull=True, title=_("Name"), alternateID=True)
    responsibleTeacher = ForeignKey('Person', default=None, title=_("Responsible teacher"))
    trend = ForeignKey('Trend', notNull=True, title=_("Trend"))
    finalTest = ForeignKey('TestType', notNull=True, title=_("Final test"))
    isFinalWork = BoolCol(notNull=True, default=False, title=_("Is final work"))
    shouldBeAMark = BoolCol(notNull=True, default=False, title=_("Should be a mark"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: self.name)

class FinalWorkTopic(ModelObject):
    """
        Topic for final work.
        Students in one group have differents final works and different final works topics.
    """

    model_title = _("Final work topic")

    name = UnicodeCol(length=180, notNull=True, title=_("Name"), alternateID=True)
    topic = ForeignKey('Topic', notNull=True, title=_("Topic"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: "%s" % self.name)

class PlanTransfer(ModelObject):
    """
        Map: Plan -> Plan.
        This map is the rule for migrations creations.
    """

    model_title = "Plan transfer"

    currentPlan = ForeignKey('Plan', notNull=True, title=_("Current plan"))
    nextPlan = ForeignKey('Plan', notNull=True, title=_("Next plan"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: "%s -> %s (%s)" % (self.currentPlan.title, self.nextPlan.title, self.comment))

class Plan(ModelObject):
    """
        Education plan for each group.
        Contains some topics inside.
    """

    model_title = None

    name = UnicodeCol(length=180, notNull=True, title=_("Name"))
    year = IntCol(notNull=True, title=_("Year"), default=constants.getAcademicYear)
    abbr = UnicodeCol(length=180, notNull=True, title=_("Abbr"))
    trend = ForeignKey('Trend', notNull=True, title=_("Trend"))
    lessonDuration = IntCol(notNull=True, title=_("Lesson duration"), default=0)
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    topics = MultipleJoin('PlanTopic')

    abbrUnique = DatabaseIndex('abbr', 'year', unique=True)
    nameUnique = DatabaseIndex('name', 'year', unique=True)

    title = property(lambda self: "%s - %d" % (self.abbr, self.year))

class PlanTopic(ModelObject):
    """
        Connective between plans and topics.
        Of cource it's m2m table with additional information inside.
    """

    model_title = _("Plan topic")

    plan = ForeignKey('Plan', notNull=True, cascade=True, title=_("Plan"))
    hours = DecimalCol(notNull=True, size=5, precision=1, title=_("Hours"))
    ordinal = IntCol(default=None, title=_("Ordinal"))  # pick default
    topic = ForeignKey('Topic', notNull=True, title=_("Topic"))
    topicOrdinalUnique = DatabaseIndex('plan', 'ordinal', unique=True)

    title = property(lambda self: "%s: (%d) %s" % (self.plan.title, self.ordinal, self.topic.title))

class TeacherTariff(ModelObject):
    """
        Dictionary class.
        Contains teacher base salary for lesson by plan with this instance.
    """

    model_title = _("Teacher tariff")

    name = UnicodeCol(length=180, notNull=True, alternateID=True, title=_("Name"))
    complexityAddition = CurrencyCol(notNull=True, title=_("Complexity addition"), default=0)
    lessonAssistantType = ForeignKey('LessonAssistantType', title=_('Lesson assistant type'), notNull=True, default=1)

    def additions(self):
        return agreement.QuantityAdditionTeacherTariff.selectBy(tariff=self)

    # todo: add getName method like this:
    # return price + comcomplexityAddition + quantityAdditions

    title = property(lambda self: self.name)

class TeacherTariffPlan(ModelObject):
    """
        M2M between TeacherTariff and Plan.
    """

    model_title = _("Teacher tariff plan")

    plan = ForeignKey('Plan', notNull=True, title=_("Plan"))
    teacherTariff = ForeignKey('TeacherTariff', notNull=True, title=_("Teacher tariff"))

    title = property(lambda self: "%s %s" % (self.plan.title, self.teacherTariff.title))

class TeacherTariffGroup(ModelObject):
    """
        M2M between TeacherTariff and Group.
    """

    model_title = _("Teacher tariff group")

    group = ForeignKey('Group', notNull=True, title=_("Group"))
    teacherTariff = ForeignKey('TeacherTariff', notNull=True, title=_("Teacher tariff"))

    title = property(lambda self: "%s %s" % (self.group.title, self.teacherTariff.title))

class Group(ModelObject):
    """
        Education group.
        Have special table name (`ed_group`) in SQL schema, because group is SQL key word.
    """

    model_title = _("Group")
    class sqlmeta:
        table = "ed_group"

    office = ForeignKey('Office', notNull=True, title=_("Office"))
    initialPlan = ForeignKey('Plan', notNull=True, title=_("Initial plan"))

    maxStudents = IntCol(notNull=True, title=_("Max students"))
    lessonPeriod = columns.DecimalCol(notNull=True, size=2, precision=1, title=_("Lesson period"))
    year = property(lambda self: self.initialPlan.year)
    ordinal = IntCol(default=None, title=_("Ordinal"))
    organization = ForeignKey('Organization', notNull=True, title=_("Organization"))  # todo: add constraints
    customerTariff = ForeignKey('CustomerTariff', notNull=True, title=_("Customer tariff"))  # todo: add constraints
    teacher = ForeignKey('Person', notNull=True, title=_("Teacher"))
    replacement1 = ForeignKey('Person', default=None, title=_("Replacement 1"))
    replacement2 = ForeignKey('Person', default=None, title=_("Replacement 2"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    cacheTitle = UnicodeCol(length=100, default='', notNull=False, title=_("Title"))
    startDate = DateCol(notNull=True, title=_("Start date"))
    endDate = DateCol(notNull=True, default=lambda: date.today(), title=_("End date"))

    isOpen = BoolCol(notNull=True, default=False, title=_("Is open"))
    withAgreement = BoolCol(notNull=True, default=False, title=_("With agreement"))
    session = IntCol(notNull=True, default=0, title=_("Session"))

    cacheStudents = IntCol(notNull=True, default=0)
    cacheScheduleDays = UnicodeCol(length=200, notNull=True, default='')

    groupNameUnique = DatabaseIndex('office', 'ordinal', 'initialPlan', unique=True)

    def getPlannedHours(self, month, year):
        my = MonthYear(month, year)

        lessons_count = len(
            [l for l in self.lessons \
             if l.date >= my.get_begin_month() and l.date <= my.get_end_month()])

        return lessons_count * self.lessonPeriod

    def replaceGroupPlan(self, plan):
        self._connection.query("CALL GroupReplacePlan(%d, %d)" % (self.id, plan.id))

        ttg_before = TeacherTariffGroup.selectBy(group=self)
        tt_after = [t.teacherTariff for t in TeacherTariffPlan.selectBy(plan=plan)]
        for ttg in ttg_before:
            if ttg.teacherTariff not in tt_after:
                ttg.destroySelf()

        self.initialPlan = plan

    def includeStudent(self, student, order, agreement, comment='', planned=False):
        sig = StudentInGroup(
            student=student,
            inclusionOrder=order,
            studentAgreement=agreement,
            discont=":" * 11,
            group=self,
        )
        Transfer(
            student=student,
            order=order,
            comment=comment,
            groupFrom=None,
            groupTo=self,
            teacherFrom=None,
            teacherTo=self.teacher,
            planned=planned
        )

        return sig

    def transferStudent(self, student, order, from_group, comment='', planned=False):
        sig = list(StudentInGroup.selectBy(student=student, group=from_group))[0]
        sig.inclusionOrder = order
        sig.group = self

        Transfer(
            student=student,
            order=order,
            comment=comment,
            groupFrom=from_group,
            groupTo=self,
            teacherFrom=from_group.teacher,
            teacherTo=self.teacher,
            planned=planned
        )

        return sig

    def isOverSizedGroup(self):
        return StudentInGroup.selectBy(group=self).count() > self.maxStudents

    def excludeStudent(self, student, order, reason, is_self_wish, comment='', planned=False):
        sig = StudentInGroup.selectBy(student=student, group=self)[0]
        agreement = sig.studentAgreement
        sig.destroySelf()

        agreement.dateCancellationAgreement = date.today()

        transfer = Transfer(student=student, order=order,
            groupFrom=self, groupTo=None,
            teacherFrom=self.teacher, teacherTo=None,
            planned=planned, comment=comment)
        Discharge(transfer=transfer, reason=reason, isSelfWish=is_self_wish)

    def getTopics(self):
        return Topic.select(SQLConstant("""
            ed_group.id = %d and
            ed_group.id = group_topic.group_id and
            group_topic.topic_id = topic.id
            """ % self.id
        ), clauseTables=('ed_group', 'topic', 'group_topic'))

    def getGroupTopics(self):
        results = GroupTopic.selectBy(group=self)
        results.orderBy = 'ordinal'

        return results

    def getLastLessonDate(self, startDate=None, lessonPeriod=None):
        # Returns date of last lesson in this education year
        # If group haven't lesson returns startDate
        dates = [x.date for x in self.getLessons(startDate, lessonPeriod)]
        if len(dates):
            return max(dates)
        return startDate

    def getLessons(self, startDate=None, lessonPeriod=None):
        result = []
        for d, gt in self.getLessonsDays(startDate, lessonPeriod):
            result.append(Wrapper(date=d, gtopic=gt))
        return result

    def getLessonByDate(self, date=date.today()):
        lessons = [l for l in self.getLessons() if l.date == date]
        if len(lessons):
            return lessons[0]
        return None

    def getSchedule(self):
        results = LessonInSchedule.selectBy(group=self)
        results.orderBy = 'dayOfWeek'

        return results

    def getDaysOfSchedule(self, start_date=None):
        lessons_starts = start_date
        schedule = list(LessonInSchedule.selectBy(group=self))
        schedule.sort(key=lambda x: x.startTimeMinute)
        schedule.sort(key=lambda x: x.startTimeHour)
        schedule.sort(key=lambda x: x.dayOfWeek)

        if not len(schedule):
            return

        cur_week_start = shift_date(lessons_starts, -lessons_starts.weekday())
        schedule_iter = iter(schedule)

        while True:
            try:
                lis = schedule_iter.next()
                day = shift_date(cur_week_start, lis.dayOfWeek - 1)
                if day.year > self.year + 1:
                    break

                if day >= lessons_starts:
                    yield day
            except StopIteration:
                schedule_iter = iter(schedule)

                # next week
                cur_week_start = date.fromordinal(cur_week_start.toordinal() + 7)

    def getGroupLessonDays(self, lessons_starts):
        holidays = set(Holiday.getHolidays(self.year))
        additionalDays = set(GroupLesson.getAdditionalDaysByGroup(self))
        removedDays = set(GroupLesson.getRemovedDaysByGroup(self))
        scheduled = set(self.getDaysOfSchedule(lessons_starts))

        return sorted((scheduled - holidays - removedDays) | additionalDays)

    def getLessonsDays(self, startDate=None, lessonPeriod=None):
        lessons_starts = self.startDate
        lesson_period = self.lessonPeriod

        if startDate is not None:
            lessons_starts = startDate
        if lessons_starts is None:
            return
        if lessonPeriod is not None:
            lesson_period = lessonPeriod
        if lesson_period is None:
            return
        # Check GroupTopics
        gtopics = list(self.gtopics)
        if not len(gtopics):
            return

        gtopics = iter(gtopics)
        cur_gtopic_hours_left = 0
        days = iter(self.getGroupLessonDays(lessons_starts))
        try:
            while True:
                day = days.next()
                cur_lesson_hours_left = lesson_period

                lesson = None  # TODO remove lesson
                while cur_lesson_hours_left > 0:
                    if cur_gtopic_hours_left <= 0:
                        cur_gtopic = gtopics.next()
                        cur_gtopic_hours_left = cur_gtopic.hours

                    if cur_lesson_hours_left >= cur_gtopic_hours_left:
                        cur_lesson_hours_left -= cur_gtopic_hours_left
                        cur_gtopic_hours_left = 0
                    else:
                        cur_gtopic_hours_left -= cur_lesson_hours_left
                        cur_lesson_hours_left = 0

                    if lesson is None:  # Lazy create lesson
                        yield (day, cur_gtopic)

        except StopIteration:
            pass

    def getShortName(self):
        return "%s-%02d-%02d-%s" % (self.initialPlan.trend.oneChar,
            self.office.ordinal, self.ordinal, self.initialPlan.abbr)

    topics = property(getTopics)
    gtopics = property(getGroupTopics)
    schedule = property(getSchedule)
    lessons = property(getLessons)

    name = property(lambda self: "%s" % self.cacheTitle)
    shortName = property(getShortName)
    title = name

    def calculateEndDate(self):
        self.endDate = self.getLastLessonDate(self.startDate)

    @classmethod
    def forAllCalculateEndDate(cls):
        for group in cls.select():
            group.calculateEndDate()

    @classmethod
    def handle_create(cls, kw, post_funcs):
        ### BIG HACK ;)
        from mnemoply import model

        plan = None
        if kw.get('initialPlanID'):
            plan = Plan.get(int(kw['initialPlanID']))
        elif kw.get('initialPlan'):
            plan = kw['initialPlan']

        customerTariff = None
        if kw.get('customerTariffID'):
            customerTariff = model.CustomerTariff.get(int(kw['customerTariffID']))
        elif kw.get('customerTariff'):
            customerTariff = kw['customerTariff']

        ct = [c.customerTariff for c in model.CustomerTariffPlan.selectBy(plan=plan)]

        if customerTariff not in ct:
            import turbogears
            turbogears.flash(_("Customer tariff ") + customerTariff.title + _(" not found"))
            raise turbogears.redirect('/Group/add')

    @classmethod
    def handle_update(cls, obj, kw):
        """
            Recalculate endDate if startDate or lessonPeriod changed.
        """

        if kw.get('startDate') or kw.get('lessonPeriod'):
            start_date = kw.get('startDate')
            lesson_period = kw.get('lessonPeriod')
            if lesson_period is not None:
                lesson_period = Decimal(lesson_period)
            if start_date is not None and start_date.__class__.__name__ == "datetime":
                start_date = start_date.date()
            kw['endDate'] = obj.getLastLessonDate(start_date, lesson_period)

class GroupTopic(ModelObject):
    """
        PlanTopic clone.
        Contains inside Group instance. We may changed local hours or topic.
    """

    model_title = _("Group topic")

    group = ForeignKey('Group', notNull=True, cascade=True, title=_("Group"))
    ordinal = IntCol(default=None, title=_("Ordinal"))
    hours = DecimalCol(default=None, size=5, precision=1, title=_("Hours"))
    topic = ForeignKey('Topic', notNull=True, title=_("Topic"))

    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    topicOrdinalUnique = DatabaseIndex('group', 'ordinal', unique=True)

    title = property(lambda self: self.topic.title)

    def getLessonsInfo(self):
        """
            Returns dict wrapper with attributes:
            lessons, start_date, end_date, schedule(date -> LessonInSchedule)
        """

        lessons = [l for l in self.group.lessons if l.gtopic == self]
        schedule = sorted([x.date for x in lessons])

        if len(lessons) > 0:
            return Wrapper(dict(
                lessons=len(lessons),
                start_date=schedule[0],
                end_date=schedule[-1],
                schedule=schedule,
            ))
        else:
            return Wrapper(dict(
                lessons=0,
                start_date=None,
                end_date=None,
                schedule={},
            ))

    @classmethod
    def handle_created(cls, kw, post_funcs):
        cls.get(kw['id']).group.calculateEndDate()

    @classmethod
    def handle_delete(cls, obj, post_funcs):
        post_funcs.append(obj.group.calculateEndDate)

    @classmethod
    def handle_update(cls, obj, kw):
        if kw.get('groupID', None) is not None or kw.get('hours', None) is not None:
            obj.cloneAndUpdateClone(kw)  # FIXME

class GroupLesson(ModelObject):
    """
        Add or delete lesson for group's schedule on specific date.
    """

    model_title = _("Group's individual lesson")

    group = ForeignKey('Group', notNull=True, title=_("Group"))
    date = DateCol(notNull=True, title=_("Date"))
    excludeLesson = BoolCol(notNull=True, default=False, title=_("Exclude from schedule"))

    groupDateUnique = DatabaseIndex('group', 'date', unique=True)

    title = property(lambda self: "%s %s %s %s" % ( \
        _("exclude") if self.excludeLesson else _("include"),
        self.date,
        _("from") if self.excludeLesson else _("to"),
        self.group.title,
    ))

    @classmethod
    def handle_created(cls, kw, post_funcs):
        cls.get(kw['id']).group.calculateEndDate()

    @classmethod
    def handle_delete(cls, obj, post_funcs):
        post_funcs.append(obj.group.calculateEndDate)

    @classmethod
    def getAdditionalDaysByGroup(cls, group):
        return [x.date for x in GroupLesson.selectBy(group=group, excludeLesson=False)]

    @classmethod
    def getRemovedDaysByGroup(cls, group):
        return [x.date for x in GroupLesson.selectBy(group=group, excludeLesson=True)]

    @classmethod
    def _getDaysByClassroom(cls, excludeLesson, classroom, startDate=None, endDate=None):
        list = [(x.date, x.group) for x in GroupLesson.selectBy(excludeLesson=excludeLesson)]
        result = []
        for l in list:
            if startDate and not endDate:
                endDate = startDate
            if startDate and endDate:
                if not (startDate <= l[0] <= endDate):
                    continue
            lesson = LessonInSchedule.selectBy(group=l[1])[0]
            if lesson.classroom == classroom:
                result.append(l + lesson.getLessonInfo())
        return result

    @classmethod
    def getAdditionalDaysByClassroom(cls, classroom, startDate=None, endDate=None):
        """
            Finds all added days in specified classroom in specified date interval
            If either startDate or endDate is specified - all days in the classroom are returned
            Returns list of tuples (date, group, classroom, startTimeHour, startTimeMinute, endTime)
        """
        return GroupLesson._getDaysByClassroom(False, classroom, startDate, endDate)

    @classmethod
    def getRemovedDaysByClassroom(cls, classroom, startDate=None, endDate=None):
        """
            Finds all removed days in specified classroom in specified date interval
            If either startDate or endDate is specified - all days in the classroom are returned
            Returns list of tuples (date, group, classroom, startTime, endTime)
        """
        return GroupLesson._getDaysByClassroom(True, classroom, startDate, endDate)

class Mark(ModelObjectWithOwner):
    """
        Student's mark for topic.
    """

    model_title = _("Mark")
    owner = ForeignKey('Person', default=None, title=_("Owner"))

    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))
    topic = ForeignKey('Topic', notNull=True, title=_("Topic"))
    hours = DecimalCol(notNull=True, size=5, precision=1, title=_("Hours"))
    student = ForeignKey('Student', notNull=True, title=_("Student"))
    markType = ForeignKey('MarkType', notNull=True, title=_("Mark type"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    studentTopicUnique = DatabaseIndex('student', 'topic', unique=True)

    title = property(lambda self: "%s (%s): %s" % (self.student.title, self.topic.title, self.markType.title))

class FinalWorkMark(ModelObjectWithOwner):
    """
        Student's mark for final work.
    """

    model_title = _("Final work mark")
    owner = ForeignKey('Person', default=None, title=_("Owner"))

    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))
    topic = ForeignKey('FinalWorkTopic', notNull=True, title=_("Final work topic"))
    hours = DecimalCol(notNull=True, size=5, precision=1, title=_("Hours"))
    student = ForeignKey('Student', notNull=True, title=_("Student"))
    markType = ForeignKey('MarkType', notNull=True, title=_("Mark type"))
    protocolNumber = UnicodeCol(length=80, default='', notNull=True, title=_("Protocol #"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    studentTopicUnique = DatabaseIndex('student', 'topic', unique=True)

    title = property(lambda self: "%s (%s): %s" % (self.student.title,
        self.topic.title, self.markType.title))

class StudentInGroup(ModelObject):
    """
        m2m between students and groups with additional information inside.
    """

    model_title = _("Student-In-Group")

    inclusionOrder = ForeignKey('Order', notNull=True, title=_("Inclusion order"))
    discont = DiscontCol(notNull=True, title=_("Discont"))
    student = ForeignKey('Student', notNull=True, title=_("Student"))
    group = ForeignKey('Group', notNull=True, title=_("Group"))
    studentAgreement = ForeignKey('StudentAgreement', notNull=True, title=_("Agreement"))
    studentGroupUnique = DatabaseIndex('student', 'group', unique=True)

    cacheBalance = CurrencyCol(notNull=True, default=0)

    title = property(lambda self: "%s-%s-%s" % (self.student.title, _('in'), self.group.title))
    name = property(lambda self: self.student.title)

    def getMarks(self):
        return Mark.select(SQLConstant("""
            mark.student_id = %d AND
            mark.topic_id = group_topic.topic_id AND
            group_topic.topic_id = topic.id AND
            group_topic.group_id = %d
            """ % (self.student.id, self.group.id)
        ), clauseTables=('mark', 'topic', 'group_topic'), orderBy='-date')

    marks = property(getMarks)

    def getDiscont(self, month):
        first_disconts = discont.FirstDiscont.selectBy(
            agreement=self.studentAgreement, isAlreadyUse=False)

        if first_disconts.count():
            fd = first_disconts[0]
            fd.isAlreadyUse = True
            return fd.discont

        return discont.Discont.get_by_null_id(self.discont[month - 1])

class Session(ModelObject):
    """
        Education session.
    """

    model_title = _("Session")

    ordinal = IntCol(notNull=True, title=_("Seesion #"))
    startTime = TimeCol(notNull=True, title=_("Start time"))
    endTime = TimeCol(notNull=True, title=_("End time"))
    classroomType = ForeignKey('ClassroomType', notNull=True, title=_("Classroom type"))

    sessionUnique = DatabaseIndex('ordinal', 'startTime', 'endTime', 'classroomType', unique=True)

    title = property(lambda self: "%d: %s-%s (%s)" % (self.ordinal, self.startTime, self.endTime, self.classroomType.title))

class LessonInSchedule(ModelObject):
    """
        Lesson in schedule representation.
    """

    model_title = _("Lesson-In-Schedule")

    group = ForeignKey('Group', notNull=True, cascade=True, title=_("Group"))
    classroom = ForeignKey('Classroom', notNull=True, title=_("Classroom"))
    dayOfWeek = IntCol(notNull=True, title=_("Day of week"))
    startTimeHour = IntCol(notNull=True, title=_("Start time: hour"))
    startTimeMinute = IntCol(notNull=True, title=_("Start time: minute"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: "%s-%02d:%02d-%s-%s" % ( \
        day_captions[self.dayOfWeek], self.startTimeHour, self.startTimeMinute,
        self.classroom.title, self.group.title
    ))

    startTime = property(lambda self: getTime(self.startTimeHour, self.startTimeMinute))
    endTime = property(lambda self: LessonInSchedule.calculateEndTime(self.startTime, self.group.initialPlan.lessonDuration))

    def getStartTimeStr(self):
        return '%02d:%02d' % (self.startTimeHour, self.startTimeMinute)

    def getEndTimeStr(self):
        return '%02d:%02d' % (self.endTime.hour, self.endTime.minute)

    def getEndLessonHour(self):
        """
            Return max hour of lesson end
            either this is endTime.hour or +1
            needed for timetable reports
        """
        if self.endTime.minute > 0:
            return self.endTime.hour + 1
        return self.endTime.hour

    def getLessonInfo(self):
        return self.classroom, self.startTimeHour, self.startTimeMinute, self.endTime

    @classmethod
    def calculateEndTime(cls, start_time, lesson_period):
        """
            Returns lesson end time, takes lesson start time and lesson duration in hours.
        """

        # Workaround, because operator (+) doesn't support for time and timedelta
        return (datetime.combine(date.today(), start_time) +
                timedelta(minutes=int(lesson_period))).time()

    @classmethod
    def handle_created(cls, kw, post_funcs):
        """
            After lesson create we recalculate end date for it group.
        """

        cls.get(kw['id']).group.calculateEndDate()

    @classmethod
    def handle_delete(cls, obj, post_funcs):
        """
            After lesson delete we recalculate group's end date.
        """

        post_funcs.append(obj.group.calculateEndDate)

    @classmethod
    def handle_create(cls, kw, post_funcs):
        cls.checkNewInstance(kw)

    @classmethod
    def handle_update(cls, obj, kw):
        """
            Instead of update object, we drop them and create same object.
            ModelObject.cloneAndUpdateClone() documentation of course is the point of interest.
        """

        obj.cloneAndUpdateClone(kw)  # FIXME

    @staticmethod
    def checkNewInstance(kw):
        # TODO
        """
            Check classroom and teacher usages in lesson period.
        """
        def getGroup(kw):
            group = None
            if 'groupID' in kw:
                group = Group.get(kw.groupID)
            elif 'group' in kw:
                group = kw.group
            return group

        def getClassroom(kw):
            classroom = None
            if 'classroomID' in kw:
                classroom = Classroom.get(kw.classroomID)
            elif 'classroom' in kw:
                classroom = kw.classroom
            return classroom

        def check(classroom, group, day_of_week, start_time, end_time):
            def checkDuplicates(ls, group, start_time, end_time, message):
                if not group.endDate:
                    raise dberrors.DuplicateEntryError("Duplicate entry. " + _("This group without end date"))
                if not ls.group.endDate:
                    raise dberrors.DuplicateEntryError("Duplicate entry. " + _("Group %s hasn't end date" % ls.group.title))
                if isIntersect(ls.group.startDate, ls.group.endDate, group.startDate, group.endDate):
                    if start_time <= ls.startTime <= end_time:
                        raise dberrors.DuplicateEntryError("Duplicate entry. " + message)

            for ls in LessonInSchedule.selectBy(classroom=classroom, dayOfWeek=day_of_week):
                checkDuplicates(ls, group, start_time, end_time, _("This classroom already used for this time period"))

            for gr in Group.selectBy(teacher=group.teacher):
                for ls in LessonInSchedule.selectBy(group=gr):
                    if ls.dayOfWeek == day_of_week:
                        checkDuplicates(ls, group, start_time, end_time, _("This teacher already work for this time period"))

        kw = Wrapper(kw)
        group = getGroup(kw)
        classroom = getClassroom(kw)

        if group is None or classroom is None:
            return

        start_time = getTime(kw.startTimeHour, kw.startTimeMinute)
        end_time = LessonInSchedule.calculateEndTime(start_time, group.initialPlan.lessonDuration)

        check(classroom, group, kw.dayOfWeek, start_time, end_time)

class Holiday(ModelObject):
    """
        Dictionary. Not works days.
    """

    model_title = _("Holiday")

    date = DateCol(notNull=True, title=_("Date"), alternateID=True)
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: str(self.date))

    @classmethod
    def getHolidaysForYear(cls, year):
        return cls.select(AND(
            cls.q.date >= date(day=1, month=1, year=year),
            cls.q.date < date(day=1, month=1, year=year + 1)
        ))

    @classmethod
    def handle_update(cls, obj, kw):
        if kw.get('date', None) is not None:
            obj.cloneAndUpdateClone(kw)  # FIXME

    @classmethod
    def handle_created(cls, kw, post_funcs):
        Group.forAllCalculateEndDate()

    @classmethod
    def handle_delete(cls, obj, post_funcs):
        post_funcs.append(Group.forAllCalculateEndDate)

    @classmethod
    def getHolidays(cls, year, startDate=None, endDate=None):
        '''
            Returns all holiday days for specified academic year.
            If startDate and endDate are specified returns holiday days from
            specified interval
        '''
        holidays_list = list(Holiday.getHolidaysForYear(year)) + \
            list(Holiday.getHolidaysForYear(year + 1))
        if startDate and endDate:
            result = [x.date for x in holidays_list if startDate <= x.date <= endDate]
        else:
            result = [x.date for x in holidays_list]
        return result

class SpentLesson(ModelObject):
    """
        Base table for teacher salary report generation.
        If teacher spent a lesson he must create a record in this table.
    """
    model_title = _("Spent lesson")

    date = DateCol(notNull=True, title=_("Date"))
    teacher = ForeignKey('Person', notNull=True, title=_("Teacher"))
    group = ForeignKey('Group', notNull=True, cascade=True, title=_("Group"))
    numStudents = IntCol(notNull=True, title=_("Number of students"))
    lessonPeriod = DecimalCol(notNull=True, size=2, precision=1, title=_("Lesson period"))
    lessonAssistantType = ForeignKey('LessonAssistantType', notNull=True, default=1, title=_("Lesson assistant type"))
    teacherTariff = ForeignKey('TeacherTariff', notNull=True, default=0, title=_("Teacher tariff"))

    title = property(lambda self: "%s %s" % (self.group.title, self.date))

class LessonAssistantType(ModelObject):
    """
        Lesson assistant type. Dictionary class.
    """

    model_title = _("Lesson assistant type")

    name = UnicodeCol(length=80, notNull=True, alternateID=True, title=_("Name"))
    stringId = UnicodeCol(length=80, default='', alternateID=True, title=_("Alternate name"))

    title = property(lambda self: self.name)

    def isAssistanceAvailable(self, group, date, teacher):
        """
            Rerurns True if assistance on this date for this teacher avaible.
        """

        if self.stringId == 'mark_checking':
            return True

        elif self.stringId == 'signer':
            if group.getLessonByDate(date.date()):
                return True

        elif self.stringId == 'default_lesson':
            if group.getLessonByDate(date.date()):
                return True

        elif self.stringId == 'defence_assistant':
            defences = project_defence.ProjectDefence.selectBy(group=group, date=date)
            for d in defences:
                if project_defence.ProjectDefenceAssistant.selectBy(defence=d, assistant=teacher).count():
                    return True

        return False

    def getAssistancePeriod(self, group, date, teacher):
        """
            By default rerutns group's lesson period.
            But if type is `project defence` returns sum of projects defences durations.
        """

        if self.stringId == 'mark_checking' or \
           self.stringId == 'signer' or \
           self.stringId == 'default_lesson':
            return group.lessonPeriod
        elif self.stringId == 'defence_assistant':
            defences = project_defence.ProjectDefence.selectBy(group=group, date=date)
            return sum([d.getDefencePeriod() for d in defences
                            if project_defence.ProjectDefenceAssistant.selectBy(defence=d, assistant=teacher).count()])
        return 0

class SalaryAddition(ModelObject):
    """
        Map between students in lesson and teacher's salary addition.
    """

    model_title = _("Salary addition")

    addition = CurrencyCol(notNull=True, title=_("Addition"), default=0)
    numStudents = IntCol(notNull=True, title=_("Number of students"))

    title = property(lambda self: "%d - %s" % (self.numStudents, format_currency(self.addition)))

class SalaryDeduction(ModelObject):
    """
        If we have record in this table for some teacher, this teacher
        doesn't have any salary additions in `startDate` --- `endDate` period.
    """

    model_title = _("Salary deduction")
    teacher = ForeignKey('Person', notNull=True, title=_("Teacher"))
    group = ForeignKey('Group', notNull=True, title=_("Group"))
    startDate = DateCol(notNull=True, title=_("Start date"))
    endDate = DateCol(notNull=True, title=_("End date"))

    title = property(lambda self: "%s %s (%s - %s)" % (self.teacher.title, self.group.title, self.startDate, self.endDate))

class SalaryBase(ModelObject):
    """
        Base for teacher's salary.
    """

    model_title = _("Salary base")

    trend = ForeignKey('Trend', notNull=True, title=_("Trend"))
    organization = ForeignKey('Organization', notNull=True, title=_("Organization"))
    amount = CurrencyCol(notNull=True, title=_("Amount"), default=0)
    year = IntCol(notNull=True, title=_("Year"), default=2010)

    title = property(lambda self: "%s %s %s %d" %
                     (self.organization.title, self.trend.title, format_currency(self.amount), self.year))

class Transfer(ModelObject):
    """
        Include to group, exclude from group or transfer student between groups.
    """

    model_title = _("Transfer (discharge, inclusion, transfer)")

    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))
    student = ForeignKey('Student', notNull=True, title=_("Student"))
    order = ForeignKey('Order', notNull=True, title=_("Order"))
    groupFrom = ForeignKey('Group', title=_("From group"))
    groupTo = ForeignKey('Group', title=_("To group"))
    teacherFrom = ForeignKey('Person', title=_("From teacher"))
    teacherTo = ForeignKey('Person', title=_("To teacher"))
    planned = BoolCol(notNull=True, default=False, title=_("Planned?"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    discharge = SingleJoin('Discharge')

    def getTitle(self):
        return "%s %s: (%s) -> (%s) (#%d)" % ( \
            str(self.date),
            self.student.title,
            self.groupFrom and self.groupFrom.title or "",
            self.groupTo and self.groupTo.title or "",
            self.order.id
        )

    title = property(getTitle)

class EducationHistory(ModelObject):
    """
        When we close group, for all students in group we create this object.
    """

    model_title = _("Education history")

    student = ForeignKey('Student', notNull=True, title=_("Student"))
    groupFrom = ForeignKey('Group', notNull=True, title=_("Group from"))
    studentAgreement = ForeignKey('StudentAgreement', notNull=True, title=_("Student agreement"))
    planAbbr = UnicodeCol(length=100, default='', notNull=True, title=_("Plan abbr"))
    groupTitle = UnicodeCol(length=100, default='', notNull=True, title=_("Group title"))
    groupOpenDate = DateCol(notNull=True, title=_("Group open date"))
    groupCloseDate = DateCol(notNull=True, title=_("Group close date"))

    title = property(lambda self: "%s %s" % (self.student.title, self.groupTitle))

class StudentMigration(ModelObject):
    """
        Temporary information for summer students transfer.
    """

    model_title = _("Student migration")

    student = ForeignKey('Student', notNull=True, title=_("Student"))
    agreement = ForeignKey('StudentAgreement', default=None, title=_("Agreement"))
    office = ForeignKey('Office', default=None, title=_("Office"))
    groupFrom = ForeignKey('Group', default=None, title=_("From group"))
    possibleDay = ForeignKey('PossibleDayValues', default=None, title=_("Possible day"))
    year = IntCol(notNull=True, title=_("Year"), default=constants.getAcademicYear)
    friends = UnicodeCol(length=200, default='', notNull=True, title=_("Friends"))
    opponents = UnicodeCol(length=200, default='', notNull=True, title=_("Opponents"))
    initialPlanTeacherWish = ForeignKey('Plan', notNull=True, default=None, title=_("Initial plan teacher wish"))
    initialPlanMethodologistWish = ForeignKey('Plan', default=None, title=_("Initial plan methodologist wish"))
    avgMark = UnicodeCol(length=80, default='', notNull=True, title=_("Average mark"))
    healthProblems = UnicodeCol(length=140, default='', notNull=True, title=_("Health problems"))
    behaviourComment = UnicodeCol(default='', notNull=True, title=_("Behaviour comment"))
    teacher = ForeignKey('Person', notNull=True, title=_('Teacher'))
    plan = ForeignKey('Plan', notNull=True, title=_("Current plan"))
    teacherWish = UnicodeCol(length=40, default='', notNull=True, title=_("Teacher wish")),
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    groupCacheTitle = UnicodeCol(length=100, default='', title=_("Group cache title"))

    migrationUnique = DatabaseIndex('student', 'groupFrom', unique=True)

    title = property(lambda self: "%s %s" % (self.student.title, self.year))

class Discharge(ModelObject):
    """
        We create this object after discharge student from group.
    """

    model_title = _("Discharge")

    transfer = ForeignKey('Transfer', notNull=True, title=_("Transfer"))
    reason = UnicodeCol(length=150, default='', notNull=True, title=_("Reason"))
    isSelfWish = BoolCol(notNull=True, default=False, title=_("Self wish?"))

    title = property(lambda self: self.transfer.title + ": " + self.reason)

class ChargeRecord(ModelObject):
    """
        Charge information. Creates once per month.
    """

    model_title = _("Charge record")

    student = ForeignKey('Student', notNull=True, title=_("Student"))
    studentAgreement = ForeignKey('StudentAgreement', notNull=True, title=_("Agreement"))
    billingMonth = IntCol(notNull=True, title=_("Billing month"))
    billingYear = IntCol(notNull=True, title=_("Billing year"))
    realHours = DecimalCol(notNull=True, size=3, precision=1, title=_("Real hours"))
    plannedHours = DecimalCol(notNull=True, size=3, precision=1, title=_("Planned hours"))
    payedHours = DecimalCol(notNull=True, size=3, precision=1, title=_("Payed hours"))
    tariff = CurrencyCol(notNull=True, title=_("Tariff"))
    discont = ForeignKey('Discont', default=None, title=_("Discont"))
    comment = UnicodeCol(default='', title=_("Comment"))

    amount = property(lambda self: applyDiscont(self.realHours * self.tariff, self.discont))

    monthYearPayerUnique = DatabaseIndex(
        'student', 'studentAgreement', 'billingMonth', 'billingYear', unique=True
    )

    @classmethod
    def getTotal(cls, student, filter_func=lambda x: True):
        """
            Summarize all charge records by student.
        """

        records = [x.amount for x in cls.selectBy(student=student) if filter_func(x)]
        return reduce(lambda a, b: a + b, records, Decimal(0))

    @staticmethod
    def createRecordForShortCourse(sig, year=None, month=None):
        """
            Create charge record for short course.
            Takes sig as instance of StudentInGroup.
            Returns new instance. If record creation isn't avaible returns None.
        """

        agr = sig.studentAgreement
        date_end = agr.dateEndAgreement

        if not year:
            year = date_end.year
        if not month:
            month = date_end.month

        if not agr.customerTariff:
            agr.customerTariff = sig.group.customerTariff

        if agr.customerTariff == sig.group.customerTariff and agr.organization == sig.group.organization:
            if ChargeRecord.isRecordCreationAvailable(sig.student, agr, year, month):
                return ChargeRecord(student=sig.student,
                                    studentAgreement=agr,
                                    billingYear=year,
                                    billingMonth=month,
                                    realHours=Decimal("1.0"),
                                    plannedHours=Decimal("1.0"),
                                    payedHours=Decimal("1.0"),
                                    tariff=agr.customerTariff.agreementCost)
            else:
                cr = ChargeRecord.selectBy(student=sig.student,
                                           studentAgreement=agr,
                                           billingYear=year,
                                           billingMonth=month)[0]
                cr.realHours = Decimal("1.0")
                cr.plannedHours = Decimal("1.0")
                cr.payedHours = Decimal("1.0")
                cr.tariff = agr.customerTariff.agreementCost
                return cr
        else:
            return None

    @staticmethod
    def isRecordCreationAvailable(student, agr, year, month):
        """
            Returns True if record creation avaible. Else returns False.
        """

        if not ChargeRecord.selectBy(student=student,
                                     studentAgreement=agr,
                                     billingYear=year,
                                     billingMonth=month).count():
            return True
        return False

    def getGroup(self):
        """
            If exists student in group for this record, returns this group.
            Else returns None.
        """

        sig = StudentInGroup.selectBy(studentAgreement=self.studentAgreement)
        if sig.count() == 1:
            return sig[0].group
        return None

    def getGroupTitle(self):
        """
            Returns group title for this record.
            If group doesn't exists, returns 'No-group'.
        """

        group = self.getGroup()
        if group is None:
            return _("No-group")
        return group.title

    title = property(lambda self: "%02d.%04d %s-%s: %s * %s" % (\
        self.billingMonth, self.billingYear,
        self.student.title, self.getGroupTitle(),
        self.realHours,
        format_currency(self.tariff),
    ))

class PaymentRecord(ModelObjectWithOwner):
    """
        Payment information.
    """

    model_title = _("Payment record")

    date = DateCol(notNull=True, default=date.today(), title=_("Date"))

    student = ForeignKey('Student', notNull=True, title=_("Payer"))
    studentAgreement = ForeignKey('StudentAgreement', notNull=True, title=_("Agreement"))
    amount = DecimalCol(notNull=True, size=10, precision=0, title=_("Amount"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    register = ForeignKey('CashRegister', notNull=True, title=_('Cash register'))

    passportNumber = UnicodeCol(length=80, default='', notNull=True, title=_("Passport number"))
    checkNumber = UnicodeCol(length=80, default='', notNull=True, title=_("Check number"))
    owner = ForeignKey('Person', default=None, title=_("Owner"))
    dateOfChanges = DateCol(default=None, title=_("Date of changes"))
    previousPayment = ForeignKey('PaymentRecord', default=None, title=_("Previous payment"))
    coupon = ForeignKey('Coupon', default=None, title=_("Coupon"))

    @classmethod
    def getTotal(cls, student, filter_func=lambda x: True):
        """
            Summarize payment record's amount per student.
        """

        records = [x.amount for x in cls.selectBy(student=student) if filter_func(x)]
        return reduce(lambda a, b: a + b, records, Decimal(0))

    def getGroup(self):
        """
            Try to return student-in-group's group.
        """

        sig = StudentInGroup.selectBy(studentAgreement=self.studentAgreement)
        if sig.count() == 1:
            return sig[0].group
        return None

    def getGroupTitle(self):
        """
            Get payment record's group title.
        """

        group = self.getGroup()
        if group is None:
            return _("No-group")
        return group.title

    title = property(lambda self: "%s %s-%s: %s" % ( \
        self.date, self.student.title, self.getGroupTitle(),
        format_currency(self.amount)
    ))

class TemporaryPaymentRecord(ModelObject):
    """
        Temporary record with special unique number.
        Needs in summer migration.
    """

    model_title = _("Temporary payment record")

    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))
    tempSurname = UnicodeCol(length=200, notNull=True, title=_("Surname"))
    tempForename = UnicodeCol(length=200, notNull=True, title=_("Forename"))
    tempPatronymic = UnicodeCol(length=200, notNull=True, title=_("Patronymic"))
    phone = UnicodeCol(length=200, notNull=True, title=_("Phone number"))
    organization = ForeignKey('Organization', default=None, title=_("Organization"))
    trend = ForeignKey('Trend', default=None, title=_("Trend"))
    discont = ForeignKey('Discont', default=None, title=_("Discont"))
    tariff = ForeignKey('CustomerTariff', default=None, title=_("Customer tariff"))
    studentAgreement = ForeignKey('StudentAgreement', default=None, title=_("Agreement"))
    student = ForeignKey('Student', default=None, title=_("Student"))
    payment = CurrencyCol(notNull=True, default=Decimal(0), title=_("Amount"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    register = ForeignKey('CashRegister', notNull=True, title=_('Cash register'))
    informationSource = ForeignKey('InformationSource', default=None, title=_('Information source'))
    event = ForeignKey('Event', default=None, title=_('Event'))
    office = ForeignKey('Office', default=None, title=_('Office'))
    isNewbie = BoolCol(notNull=True, default=True, title=_("Is newbie"))

    title = property(lambda self: "%d" % self.id)

    @classmethod
    def handle_created(cls, kw, post_funcs):
        import turbogears
        default_office = turbogears.identity.current.user.office
        if not default_office:
            default_office = Office.get(0) # FIXME
        cls.get(int(kw['id'])).office = default_office

    @classmethod
    def handle_update(cls, obj, kw):
        if kw.get('payment', None) is not None and kw['payment'] != u'0.00' and obj.payment == Decimal(0):
            kw['date'] = date.today()

    @classmethod
    def handle_delete(cls, obj, post_funcs):
        if obj.payment > 0:
            import cherrypy, turbogears
            turbogears.flash(_("Can't be removed: payment > 0 "))
            raise turbogears.redirect(cherrypy.request.headerMap['Referer'])

class Reservist(ModelObject):
    """
        Reservist. Information about possile students.
    """

    model_title = _("Reservist")

    # person information
    surname = UnicodeCol(length=80, notNull=True, title=_("Surname"))
    forename = UnicodeCol(length=80, notNull=True, title=_("Forename"))
    patronymic = UnicodeCol(length=80, default='', notNull=True, title=_("Patronymic"))
    email = UnicodeCol(length=80, default='', notNull=True, title=_("e-mail"))
    phone = UnicodeCol(length=80, notNull=True, title=_("Phone"))

    # interests and wishes
    interestedOffice = ForeignKey('Office', default=None, title=_("Interested office"))
    interestedTrend = ForeignKey('Trend', default=None, title=_("Interested trend"))
    interestedPlan = ForeignKey('Plan', default=None, title=_("Interested plan"))
    wishes = UnicodeCol(default='', notNull=True, title=_("Wishes"))

    # recommendations
    possibleGroup = ForeignKey('Group', default=None, title=_("Possible group"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    lastPhoneCallTime = DateCol(notNull=True, default=date.today(), title=_("Last phone call date"))
    isStudent = BoolCol(default=False, title=_("Is student?"))

    customerName = UnicodeCol(length=200, default='', notNull=True, title=_("Customer name"))
    temporaryPaymentRecord = ForeignKey('TemporaryPaymentRecord', default=None, title=_("Temporary payment record"))

    date = DateCol(notNull=True, default=date.today(), title=_("Creation date"))

    title = property(lambda self: "%s %s %s" % (self.surname, self.forename, self.patronymic))

class PreorderGroupIncData(ModelObject):
    model_title = _("Preorder: GroupInclusion")

    preorder = ForeignKey('Preorder', notNull=True)
    student = ForeignKey('Student', notNull=True)
    group = ForeignKey('Group', notNull=True)
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    preorderStudentUnique = DatabaseIndex('preorder', 'student', unique=True)

    def getTitle(self):
        title = "%s -> %s" % (self.student.title, self.group.title)

        if self.comment:
            title += " (%s)" % self.comment

        return title
    title = property(getTitle)

class PreorderEndEducationData(ModelObject):
    model_title = _("Preorder: EndEducation")

    preorder = ForeignKey('Preorder', notNull=True)
    student = ForeignKey('Student', notNull=True)

    preorderStudentUnique = DatabaseIndex('preorder', 'student', unique=True)

    def getTitle(self):
        return self.student.title
    title = property(getTitle)

class PreorderGroupDisData(ModelObject):
    model_title = _("Preorder: GroupDischarge")

    preorder = ForeignKey('Preorder', notNull=True)
    student = ForeignKey('Student', notNull=True)
    group = ForeignKey('Group', notNull=True)

    reason = UnicodeCol(length=150, default='', notNull=True)
    isSelfWish = BoolCol(notNull=True, default=False)
    planned = BoolCol(notNull=True, default=False)
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    preorderStudentUnique = DatabaseIndex('preorder', 'student', unique=True)

    def getTitle(self):
        title = "%s %s %s (" % (self.student.title, _("from"), self.group.title)

        if self.reason:
            title += '%s:"%s" ' % (_("reason"), self.reason)

        title += '%s:%s ' % (_("self wish"), self.isSelfWish and _('yes') or _('no'))
        title += '%s:%s' % (_("planned"), self.planned and _('yes') or _('no'))

        if self.comment:
            title += ' %s:"%s"' % (_("comment"), self.comment)

        return title + ')'
    title = property(getTitle)

class PreorderGroupTransData(ModelObject):
    model_title = _("Preorder: GroupTransfer")

    preorder = ForeignKey('Preorder', notNull=True)
    student = ForeignKey('Student', notNull=True)
    group_from = ForeignKey('Group', notNull=True)
    group_to = ForeignKey('Group', notNull=True)

    preorderStudentUnique = DatabaseIndex('preorder', 'student', unique=True)

    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    def getTitle(self):
        title = "%s: %s -> %s" % (self.student.title, self.group_from.title, self.group_to.title)

        if self.comment:
            title += " (%s)" % self.comment

        return title
    title = property(getTitle)

class DocumentNumber(ModelObject):
    """
        Create this number after student's education ending.
    """

    model_title = _("Document number")

    office = ForeignKey('Office', notNull=True)
    year = IntCol(notNull=True)
    last_number = IntCol(notNull=True)

    title = property(lambda self: "%s %d: %d" % (self.office.title, self.year, self.last_number))

    @classmethod
    def getNewOrdinal(cls, office):
        """
            Create unique ordinal per office.
        """

        cur_year = date.today().year

        dnumber = getOne(cls.selectBy(office=office, year=cur_year))

        if dnumber is None:
            DocumentNumber(
                office=office,
                year=cur_year,
                last_number=1,
            )
            return 1
        else:
            dnumber.last_number += 1
            return dnumber.last_number

class AvgMarkValues(ModelObject):
    """
        Average marks values.
    """

    model_title = _("AvgMark values")

    value = UnicodeCol(length=100, notNull=True, alternateID=True, title=_("Value"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: self.value)

class PossibleDayValues(ModelObject):
    """
        Possible days for education.
    """

    model_title = _("PossibleDay values")

    value = UnicodeCol(length=100, notNull=True, alternateID=True, title=_("Value"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: self.value)

class PhoneCode(ModelObject):
    """
        Possible code from operators
    """

    model_title = _("Phone codes")

    value = UnicodeCol(length=5, notNull=True, alternateID=True, title=_("Value"))
    name = UnicodeCol(default='', notNull=True, title=_("Name"))

    title = property(lambda self: self.name)
class CertificateDelivery(ModelObject):
    """
        Final document for certificate. Create after certificate's tests passed.
    """

    model_title = _("Certificate delivery")

    student = ForeignKey('Student', notNull=True, title=_("Student"))
    certificate = ForeignKey('Certificate', notNull=True, title=_("Certificate"))
    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))
    takeIn = BoolCol(notNull=True, default=False, title=_("Take in?"))

    uniqueCertificateDelivery = DatabaseIndex('studentID', 'certificateID', 'date', unique=True)

    title = property(lambda self: "%s %s %s" % (self.student.title, self.certificate.title, format_date(self.date)))

class TestDelivery(ModelObject):
    """
        Final document for test, not a certificate.
    """

    model_title = _("Test delivery")

    student = ForeignKey('Student', notNull=True, title=_("Student"))
    test = ForeignKey('TestName', notNull=True, title=_("Test name"))
    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))
    grade = IntCol(notNull=True, title=_("Grade"))

    uniqueCertificateDelivery = DatabaseIndex('studentID', 'testID', 'date', unique=True)

    title = property(lambda self: "%s %s %s" % (self.student.title, self.test.title, format_date(self.date)))

class MSITAcademyCourse(ModelObject):
    """
        Special MS IT course.
    """

    model_title = _("MS IT Academy products")
    class sqlmeta:
        table = "ms_it_academy"

    courceName = UnicodeCol(length=100, notNull=True, alternateID=True, title=_("Cource name"))
    topic = ForeignKey('Topic', notNull=True, title=_("Topic"))

    title = property(lambda self: self.courceName)

class New(ModelObject):
    """
        New for personal. Usually contains information about server updates.
    """

    model_title = _("New")

    content = UnicodeCol(default='', notNull=True, title=_("Content"))
    forWhom = UnicodeCol(length=100, default='', notNull=True, title=_("For whom"))
    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))

    title = property(lambda self: "%s" % format_date(self.date))

class CashRegister(ModelObject):
    """Cash register representation."""

    model_title = _("Cash register")

    name = UnicodeCol(length=80, notNull=True, title=_("Name"))
    office = ForeignKey('Office', notNull=True, title=_("Office"))
    organization = ForeignKey('Organization', notNull=True, title=_("Organization"))

    uniqueName = DatabaseIndex('name', unique=True)
    uniqueOfficeOrganization = DatabaseIndex('office', 'organization', unique=True)

    title = property(lambda self: "%s" % self.name)

class CashRegisterDepartment(ModelObject):
    """Cash register department representation."""

    model_title = _("Cash register department")

    name = ForeignKey('CashRegisterDepartmentName', notNull=True, title=_("Cash register department name"))
    cashRegister = ForeignKey('CashRegister', notNull=True, title=_("Cash register"))
    ordinal = IntCol(default=None, title=_("Ordinal"))

    ordinalUnique = DatabaseIndex('ordinal', 'cashRegister', unique=True)

    title = property(lambda self: "%s: %s" % (self.cashRegister.title, self.name.title))

class CashRegisterDepartmentName(ModelObject):
    """Names for cash register departments."""

    model_title = _("Cash register department name")

    name = UnicodeCol(length=80, notNull=True, title=_("Name"))
    nameUnique = DatabaseIndex('name', unique=True)

    title = property(lambda self: "%s" % self.name)

class CashRegisterDepartmentTrend(ModelObject):
    """M2M between CashRegisterDepartment and Trend"""

    model_title = _("Cash register department trend")

    department = ForeignKey('CashRegisterDepartment', notNull=True, title=_("Cash register department"))
    trend = ForeignKey('Trend', notNull=True, title=_("Trend"))

    ordinalUnique = DatabaseIndex('department', unique=True)

    title = property(lambda self: "%s - %s" % (self.department.title, self.trend.title))

class BondRecord(ModelObject):
    """Contains information about cash amount in cash registers."""

    model_title = _("Bond record")

    register = ForeignKey('CashRegister', notNull=True, title=_("Cash register"))
    dateOfBegin = DateCol(notNull=True, default=lambda: date.today(), title=_("Date of begin"))
    dateOfCollection = DateCol(notNull=True, default=lambda: date.today(), title=_("Date of collection"))
    bond10 = IntCol(default=0, title=_("Bond 10"))
    bond50 = IntCol(default=0, title=_("Bond 50"))
    bond100 = IntCol(default=0, title=_("Bond 100"))
    bond500 = IntCol(default=0, title=_("Bond 500"))
    bond1000 = IntCol(default=0, title=_("Bond 1000"))
    bond5000 = IntCol(default=0, title=_("Bond 5000"))
    numBoxOfCollection = UnicodeCol(length=20, notNull=True, default='0', title=_("Number box of collection"))

    recordInRegisterUnique = DatabaseIndex('dateOfCollection', 'register', 'dateOfBegin', unique=True)

    title = property(lambda self: "%s: %s" % (self.dateOfCollection, self.register.title))

class ServiceType(ModelObject):
    """Type of one-time service."""

    model_title = _("Service type")

    name = UnicodeCol(length=80, notNull=True, title=_("Name"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: self.name)

class Service(ModelObject):
    """One-time service representation."""

    model_title = _("Service")

    name = UnicodeCol(length=80, notNull=True, title=_("Name"))
    serviceType = ForeignKey('ServiceType', notNull=True, title=_("Service type"))
    cost = IntCol(notNull=True, title=_("Cost of service"))

    title = property(lambda self: "%s: %s %s" % (self.serviceType.title, self.name, format_currency(self.cost)))

class ServiceAgreement(ModelObject):
    """Agreement for one-time service."""

    model_title = _("Service agreement")

    organization = ForeignKey('Organization', notNull=True, title=_("Organization"))
    conclusionDate = DateCol(notNull=True, title=_("Date of conclusion"), default=date.today())
    customer = ForeignKey('Customer', notNull=True, title=_("Customer"), default=None)
    service = ForeignKey('Service', notNull=True, title=_("Service"))
    department = ForeignKey('CashRegisterDepartment', notNull=True, title=_("Cash register department"))
    cost = IntCol(notNull=True, title=_("Cost of agreement"))

    title = property(lambda self: "%s %s" % (self.customer.title, self.service.title))

class ServicePaymentRecord(ModelObject):
    """Payment record for one-time service."""

    model_title = _("Service payment record")

    agreement = ForeignKey('ServiceAgreement', notNull=True, title=_("Service agreement"))
    cost = IntCol(notNull=True)
    paymentDate = DateCol(notNull=True, title=_("Payment date"), default=date.today())
    cashRegister = ForeignKey('CashRegister', notNull=True, title=_("Cash register"))

    title = property(lambda self: "%s: %s" % (self.agreement.title, format_date(self.paymentDate)))

class InformationSource(ModelObject):
    """Information source for marketing department."""

    model_title = _("Information sourse")

    name = UnicodeCol(length=200, notNull=True, title=_("Name"))
    stringId = UnicodeCol(length=80, default='', title=_("Alternate name"))
    abbr = UnicodeCol(length=100, notNull=True, default='', title=_("Abbr"))
    isActive = BoolCol(notNull=True, default=True, title=_("Is active?"))

    @classmethod
    def byStringId(cls, str_id):
        return cls.selectBy(stringId=str_id)[0]

    title = property(lambda self: "%s %s" % (self.abbr, self.name))

class Event(ModelObject):
    """Event representation."""

    model_title = _("Event")

    date = DateCol(notNull=True, default=lambda: date.today(), title=_("Date"))
    office = ForeignKey('Office', notNull=True, title=_("Office"))
    trend = ForeignKey('Trend', notNull=True, title=_("Trend"))
    numberOfEmployees = IntCol(notNull=True, title=_("Numeber of employees"))
    numberOfClients = IntCol(notNull=True, title=_("Numeber of clients"))

    title = property(lambda self: "%s %s %s" % (self.office.title, self.trend.title, self.date))

class CouponType(ModelObject):
    """Coupon type."""

    model_title = _("Coupon type")

    name = UnicodeCol(length=200, notNull=True, title=_("Name"))
    nominal = IntCol(notNull=True, title=_("Nominal"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))
    year = IntCol(notNull=True, title=_("Year"), default=date.today().year)

    title = property(lambda self: "%d - %s" %(self.year,self.name))

class Coupon(ModelObject):
    """Coupon for marketing department."""

    model_title = _("Coupon")

    checksum = IntCol(notNull=True, title=_("Checksum"))
    dateBegin = DateCol(notNull=True, default=lambda: date.today(), title=_("Date begin"))
    dateEnd = DateCol(notNull=True, default=lambda: date.today(), title=_("Date end"))
    temporaryPaymentRecord = ForeignKey('TemporaryPaymentRecord', default=None, title=_("Temporary payment record"))
    studentAgreement = ForeignKey('StudentAgreement', notNull=True, title=_("Who used"))
    type = ForeignKey('CouponType', notNull=True, title=_("Coupon type"))
    isCancel = BoolCol(notNull=True, default=True, title=_("Is cancel?"))
    cancellationDate = DateCol(notNull=True, default=lambda: date.today(), title=_("Cancellation date"))
    owner = ForeignKey('Person', default=None, title=_("Owner"))
    comment = UnicodeCol(default='', notNull=True, title=_("Comment"))

    title = property(lambda self: "%s: %d" % (self.type.title,
    self.id))

    def isActive(self):
       try:
           ca = CouponActive.get(self.id)
           return ca is not None and self.studentAgreement is None
       except SQLObjectNotFound:
           return False
        

    def Apply(self,agreement):
   #  """Returns True if coupon was applied correctly. Otherwise returns False."""

       if self.isActive():
              student = agreement.student
              register = 0
               
              amount = self.type.nominal
              #INSERT INTO payment_record
              PaymentRecord(
              coupon = self,
              student=student,
              studentAgreement=agreement,
              amount=amount,
              register=register,
              date=date.today(),
              )
              #UPDATE student_agreement
              agreement.dateLastCoupon = date.today()
              #UPDATE coupon
              self.studentAgreement = agreement
              return True
       return False

class CouponActive(ModelObject):
    """Only active coupons"""

    model_title = _("Coupon")
    class sqlmeta:
        table = "coupon_active"

    checksum = IntCol(notNull=True, title=_("Checksum"))
    type = ForeignKey('CouponType', notNull=True, title=_("Coupon type"))
    name = UnicodeCol(length=50, default='00', notNull=True, title=_("Name"))
    nominal = IntCol(notNull=True, title=_("Nominal"))
    title = property(lambda self: "%s: %d" % (self.name, self.id))

class TerminalPayment(ModelObject):
    """Record from terminal_payments table."""
    model_title = _("Terminal payment")

    class sqlmeta:
        table = "terminal_payments"

    register = ForeignKey('CashRegister', default=None, title=_("Cash register"))
    barCode = UnicodeCol(length=50, default='00', notNull=True, title=_("Comment"))
    stringInfo = UnicodeCol(length=50, notNull=True, default='', title=_("String info"))
    checkInfo = UnicodeCol(length=255, notNull=True, default='', title=_("Check info"))
    sumCheck = IntCol(notNull=True, default=0, title=_("Sum check"))
    sumCoupon = IntCol(notNull=True, default=0, title=_("Sum coupon"))

    title = property(lambda self: "")
